/**
 * 
 */
package kr.co.insoft.auth.mapper.mail;

import java.util.HashSet;

import kr.co.insoft.auth.mapper.initialize.InitializeTableMapper;
import kr.co.insoft.auth.model.initialize.InitializeTableModel;
import kr.co.insoft.auth.model.mail.MaillingFormModel;
import kr.co.insoft.auth.service.initalize.InitializeTableService;
import kr.co.insoft.framework.mybatis.Mapper;

import org.apache.ibatis.annotations.Delete;
import org.apache.ibatis.annotations.Insert;
import org.apache.ibatis.annotations.Select;
import org.apache.ibatis.annotations.Update;

/**
 * @author IN-SOFT Inc. GoodwillDD(sylee@in-soft.co.kr)
 * 
 */
@Mapper
public interface MaillingMapper extends InitializeTableMapper {
	@Override
	@Insert("CREATE TABLE `"
			+ InitializeTableService.MAILLING
			+ "` (`seq` BIGINT(20) NOT NULL AUTO_INCREMENT, `userid` BIGINT(20) NOT NULL, `mailtype` INT(11) NOT NULL, `createdTime` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, `confirmKey` VARCHAR(500), `mailStatus` BIT(1) NOT NULL DEFAULT b'0', PRIMARY KEY (`seq`))")
	public void createTable() throws Exception;

	@Override
	@Select("SELECT count(*) as `"
			+ InitializeTableService.MAILLING
			+ "` FROM information_schema.tables WHERE `TABLE_SCHEMA` = #{datasource} AND `TABLE_NAME` = #{tablename}")
	public Integer isExistTable(InitializeTableModel initializeTableModel)
			throws Exception;

	@Insert("INSERT INTO `"
			+ InitializeTableService.MAILLING
			+ "` (`userid`, `mailtype`,`confirmKey`) VALUES(#{userid},#{mailtype}, #{confirmKey})")
	public void sendRegistedMail(MaillingFormModel maillingFormModel)
			throws Exception;

	/**
	 * 발송할 메일 내역을 가지고 온다.
	 */
	@Select("SELECT * FROM `" + InitializeTableService.MAILLING
			+ "` WHERE `mailStatus` = 0 ORDER BY `seq` ASC LIMIT 0, 5")
	public HashSet<MaillingFormModel> checkMails();

	/**
	 * @param maillingFormModel
	 */
	@Update("UPDATE  `" + InitializeTableService.MAILLING
			+ "` SET `mailStatus` = 1 WHERE `seq`=#{seq}")
	public void completedMails(MaillingFormModel maillingFormModel);

	/**
	 * 메일을 발송한지 1시간이 지난 메일 삭제
	 * 
	 * @throws Exception
	 */
	@Delete("DELETE FROM `"
			+ InitializeTableService.MAILLING
			+ "` WHERE `mailStatus`=1 AND TIMESTAMPDIFF(HOUR, `createdTime`, now()) > 0")
	public void cleanMails() throws Exception;

	/**
	 * @param confirmKey
	 * @return
	 */
	@Select("SELECT * FROM `" + InitializeTableService.MAILLING
			+ "` WHERE `confirmKey`=#{confirmKey}")
	public MaillingFormModel getMailByConfirmKey(String confirmKey)
			throws Exception;

	/**
	 * @param seq
	 */
	@Delete("DELETE FROM `" + InitializeTableService.MAILLING
			+ "` WHERE `seq`=#{seq}")
	public void cleanMail(Long seq) throws Exception;
}
